﻿// --------------------------------------------------------------------------------------------------------------------
// <copyright file="SqlServerDomainContext.cs" company="Microsoft">
//   Microsoft
// </copyright>
// <summary>
//   Domain service that exposes methods to connect to SQL Server Denali
// </summary>
// Copyright (c) Microsoft Corporation. All rights reserved.
// 
// THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, 
// EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES 
// OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.
// ----------------------------------------------------------------------------------
// The example companies, organizations, products, domain names,
// e-mail addresses, logos, people, places, and events depicted
// herein are fictitious.  No association with any real company,
// organization, product, domain name, email address, logo, person,
// places, or events is intended or should be inferred.
// --------------------------------------------------------------------------------------------------------------------

namespace MySemanticSearch.Web.DataAccess
{
    using System;
    using System.Collections;
    using System.Collections.Generic;
    using System.Data.SqlClient;
    using System.Data.SqlTypes;
    using System.Globalization;
    using System.Linq;
    using System.ServiceModel.DomainServices.Hosting;
    using System.ServiceModel.DomainServices.Server;
    using System.Transactions;
    using System.Web.Management;

    using Microsoft.SqlServer.Management.Smo;

    using MySemanticSearch.Web.QueryBuilder;

    // TODO: Create methods containing your application logic.

    /// <summary>
    /// Domain service that exposes methods to connect to SQL Server Denali
    /// </summary>
    [EnableClientAccess]
    public class SqlServerDomainService : DomainService
    {
        /// <summary>
        /// Maximum number of decimals
        /// </summary>
        private static int roundScore = 5;

        /// <summary>
        /// Gets tags with highest score in the table.
        /// </summary>
        /// <param name="sqlInstance">
        /// The sql Instance.
        /// </param>
        /// <param name="database">
        /// The database.
        /// </param>
        /// <param name="fileTable">
        /// The file Table.
        /// </param>
        /// <param name="tagCount">
        /// The tag count.
        /// </param>
        /// <returns>
        /// Tags with highest score in the table.
        /// </returns>
        public IDictionary<string, double> GetTags(string sqlInstance, string database, string fileTable, int tagCount)
        {
            var tags = new Dictionary<string, double>();

            using (var connection = ConnectServerInstance(sqlInstance, database).ConnectionContext.SqlConnectionObject)
            {
                var query = String.Format(CultureInfo.InvariantCulture, App_Data.SqlQueries.GetTags, fileTable);
                using (var command = new SqlCommand(query, connection))
                {
                    try
                    {
                        command.Parameters.AddWithValue("@TagCount", tagCount);

                        using (var reader = command.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                tags[reader.GetString(0)] = Math.Round(reader.GetDouble(1), roundScore);
                            }
                        }
                    }
                    catch
                    {
                        var errorInformation = String.Format(CultureInfo.CurrentCulture, App_Data.ExceptionMessages.FormatDatabase, database) + Environment.NewLine +
                            String.Format(CultureInfo.CurrentCulture, App_Data.ExceptionMessages.FormatFileTable, fileTable) + Environment.NewLine +
                            String.Format(CultureInfo.CurrentCulture, App_Data.ExceptionMessages.FormatTagCount, tagCount);
                        var errorMessage = App_Data.ExceptionMessages.GetTagsError;

                        throw new SqlExecutionException(errorMessage + Environment.NewLine + errorInformation);
                    }
                }
            }

            return tags;
        }

        /// <summary>
        /// Upload a document to the database.
        /// </summary>
        /// <param name="sqlInstance">
        /// The sql Instance.
        /// </param>
        /// <param name="database">
        /// The database.
        /// </param>
        /// <param name="fileTable">
        /// The file Table.
        /// </param>
        /// <param name="stream">
        /// The stream.
        /// </param>
        /// <param name="fileName">
        /// The filename.
        /// </param>
        /// <returns>
        /// True. (because DomainService methods cannot return void)
        /// </returns>
        public bool UploadDocument(string sqlInstance, string database, string fileTable, byte[] stream, string fileName)
        {
            using (var scope = new TransactionScope())
            {
                using (var connection = ConnectServerInstance(sqlInstance, database).ConnectionContext.SqlConnectionObject)
                {
                    var query = String.Format(CultureInfo.InvariantCulture, App_Data.SqlQueries.UploadDocument, fileTable);
                    using (var command = new SqlCommand(query, connection))
                    {
                        try
                        {
                            command.Parameters.AddWithValue("@filestream", stream);
                            command.Parameters.AddWithValue("@filename", fileName);

                            command.ExecuteNonQuery();

                            scope.Complete();
                        }
                        catch
                        {
                            var errorInformation = String.Format(CultureInfo.CurrentCulture, App_Data.ExceptionMessages.FormatDatabase, database) + Environment.NewLine +
                                String.Format(CultureInfo.CurrentCulture, App_Data.ExceptionMessages.FormatFileTable, fileTable) + Environment.NewLine +
                                String.Format(CultureInfo.CurrentCulture, App_Data.ExceptionMessages.FormatFilename, fileName);
                            var errorMessage = App_Data.ExceptionMessages.UploadDocumentError;

                            throw new SqlExecutionException(errorMessage + Environment.NewLine + errorInformation);
                        }
                    }
                }
            }

            return true;
        }

        /// <summary>
        /// Downloads a document from the database.
        /// </summary>
        /// <param name="sqlInstance">
        /// The sql Instance.
        /// </param>
        /// <param name="database">
        /// The database.
        /// </param>
        /// <param name="fileTable">
        /// The file Table.
        /// </param>
        /// <param name="fileName">
        /// The filename.
        /// </param>
        /// <returns>
        /// Document data stream
        /// </returns>
        public byte[] DownloadDocument(string sqlInstance, string database, string fileTable, string fileName)
        {
            using (var connection = ConnectServerInstance(sqlInstance, database).ConnectionContext.SqlConnectionObject)
            {
                var query = String.Format(CultureInfo.InvariantCulture, App_Data.SqlQueries.GetDocument, fileTable);
                using (var command = new SqlCommand(query, connection))
                {
                    try
                    {
                        command.Parameters.AddWithValue("@filename", fileName);

                        return (byte[])command.ExecuteScalar();
                    }
                    catch
                    {
                        var errorInformation = String.Format(CultureInfo.CurrentCulture, App_Data.ExceptionMessages.FormatDatabase, database) + Environment.NewLine +
                            String.Format(CultureInfo.CurrentCulture, App_Data.ExceptionMessages.FormatFileTable, fileTable) + Environment.NewLine +
                            String.Format(CultureInfo.CurrentCulture, App_Data.ExceptionMessages.FormatFilename, fileName);
                        var errorMessage = App_Data.ExceptionMessages.DownloadDocumentError;

                        throw new SqlExecutionException(errorMessage + Environment.NewLine + errorInformation);
                    }
                }
            }
        }

        /// <summary>
        /// Get all documents from the database.
        /// </summary>
        /// <param name="sqlInstance">
        /// The sql Instance.
        /// </param>
        /// <param name="database">
        /// The database.
        /// </param>
        /// <param name="filetable">
        /// The filetable.
        /// </param>
        /// <returns>
        /// Documents in the database.
        /// </returns>
        public IDictionary<string, string> GetDocuments(string sqlInstance, string database, string filetable)
        {
            var query = String.Format(CultureInfo.InvariantCulture, App_Data.SqlQueries.GetDocuments, filetable);
            using (var command = new SqlCommand(query))
            {
                return GetDocumentsCommand(sqlInstance, database, command);
            }
        }
        
        /// <summary>
        /// Queries the data base using the conditions in the input string
        /// </summary>
        /// <param name="sqlInstance">
        /// The sql instance.
        /// </param>
        /// <param name="database">
        /// The database.
        /// </param>
        /// <param name="fileTable">
        /// The file table.
        /// </param>
        /// <param name="input">
        /// The input.
        /// </param>
        /// <returns>
        /// A dictionary of documents that match the given conditions
        /// </returns>
        public IDictionary<string, string> GetSearchResults(string sqlInstance, string database, string fileTable, string input)
        {
            var parser = new QueryParser(new ConditionBuilder(), fileTable);
            var query = String.Format(CultureInfo.InvariantCulture, App_Data.SqlQueries.GetSearchResults, fileTable) + " " + parser.ParseQuery(input);
            using (var command = new SqlCommand(query))
            {
                try
                {
                    return GetDocumentsCommand(sqlInstance, database, command);
                }
                catch (Exception)
                {
                    var errorInformation = String.Format(CultureInfo.CurrentCulture, App_Data.ExceptionMessages.FormatDatabase, database) + Environment.NewLine +
                            String.Format(CultureInfo.CurrentCulture, App_Data.ExceptionMessages.FormatFileTable, fileTable) + Environment.NewLine +
                            String.Format(CultureInfo.CurrentCulture, App_Data.ExceptionMessages.FormatTag);
                    var errorMessage = App_Data.ExceptionMessages.GetTagsDocumentError;

                    throw new SqlExecutionException(errorMessage + Environment.NewLine + errorInformation);
                }
            }
        }

        /// <summary>
        /// Delete a document from the database.
        /// </summary>
        /// <param name="sqlInstance">
        /// The sql Instance.
        /// </param>
        /// <param name="database">
        /// The database.
        /// </param>
        /// <param name="fileTable">
        /// The file Table.
        /// </param>
        /// <param name="fileName">
        /// The filename.
        /// </param>
        /// <returns>
        /// True. (because DomainService methods cannot return void)
        /// </returns>
        public bool DeleteDocument(string sqlInstance, string database, string fileTable, string fileName)
        {
            using (var scope = new TransactionScope())
            {
                using (var connection = ConnectServerInstance(sqlInstance, database).ConnectionContext.SqlConnectionObject)
                {
                    var query = String.Format(CultureInfo.InvariantCulture, App_Data.SqlQueries.DeleteDocument, fileTable);
                    using (var command = new SqlCommand(query))
                    {
                        try
                        {
                            command.Connection = connection;
                            command.Parameters.AddWithValue("@FileName", fileName);

                            command.ExecuteNonQuery();

                            scope.Complete();
                        }
                        catch
                        {
                            var errorInformation = String.Format(CultureInfo.CurrentCulture, App_Data.ExceptionMessages.FormatDatabase, database) + Environment.NewLine +
                                String.Format(CultureInfo.CurrentCulture, App_Data.ExceptionMessages.FormatFileTable, fileTable) + Environment.NewLine +
                                String.Format(CultureInfo.CurrentCulture, App_Data.ExceptionMessages.FormatFilename, fileName);
                            var errorMessage = App_Data.ExceptionMessages.DeleteDocumentError;

                            throw new SqlExecutionException(errorMessage + Environment.NewLine + errorInformation);
                        }
                    }
                }
            }

            return true;
        }

        /// <summary>
        /// Retrieves all search properties from the resources
        /// </summary>
        /// <param name="sqlInstance">
        /// The sql Instance.
        /// </param>
        /// <param name="database">
        /// The database.
        /// </param>
        /// <param name="fileTable">
        /// The file Table.
        /// </param>
        /// <returns>
        /// A list of search properties
        /// </returns>
        public IEnumerable<string> GetSearchProperties(string sqlInstance, string database, string fileTable)
        {
            var list = new List<string>();

            using (var connection = ConnectServerInstance(sqlInstance, database).ConnectionContext.SqlConnectionObject)
            {
                using (var command = new SqlCommand(App_Data.SqlQueries.GetFiletableSearchProperties))
                {
                    try
                    {
                        command.Parameters.AddWithValue("@Filetable", fileTable);
                        command.Connection = connection;

                        using (var reader = command.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                list.Add(reader.GetString(0));
                            }
                        }
                    }
                    catch
                    {
                        var errorInformation = String.Format(CultureInfo.CurrentCulture, App_Data.ExceptionMessages.FormatDatabase, database) + Environment.NewLine +
                            String.Format(CultureInfo.CurrentCulture, App_Data.ExceptionMessages.FormatFileTable, fileTable);
                        var errorMessage = App_Data.ExceptionMessages.GetSearchPropertiesError;

                        throw new SqlExecutionException(errorMessage + Environment.NewLine + errorInformation);
                    }
                }
            }

            return list;
        }
        
        /// <summary>
        /// Retrieves all used databases from the service
        /// </summary>
        /// <param name="sqlInstance">
        /// The sql Instance.
        /// </param>
        /// <param name="hasFileTable">
        /// The has File Table.
        /// </param>
        /// <returns>
        /// A dictionary with all databases
        /// </returns>
        public List<string> GetDatabaseNameList(string sqlInstance, bool hasFileTable)
        {
            var sqlServer = ConnectServerInstance(sqlInstance);

            try
            {
                var filetableDbs = new List<string>();

                foreach (var database in sqlServer.Databases.Cast<Database>().Where(database => (!database.IsSystemObject && database.IsFullTextEnabled) && !String.IsNullOrEmpty(database.FilestreamDirectoryName)))
                {
                    if (!hasFileTable)
                    {
                        filetableDbs.Add(database.Name);
                    }
                    else if (this.CountFiletables(sqlInstance, database.Name) > 0)
                    {
                        filetableDbs.Add(database.Name);
                    }
                }

                return filetableDbs;
            }
            catch
            {
                throw new SmoException(App_Data.ExceptionMessages.GetDatabaseNameListError);
            }
        }

        /// <summary>
        /// Returns the subset of databases with filetables
        /// </summary>
        /// <param name="sqlInstance">
        /// The sql Instance.
        /// </param>
        /// <param name="database">
        /// The database.
        /// </param>
        /// <returns>
        /// The filetable database list.
        /// </returns>
        public int CountFiletables(string sqlInstance, string database)
        {
            using (var connection = ConnectServerInstance(sqlInstance, database).ConnectionContext.SqlConnectionObject)
            {
                var query = String.Format(CultureInfo.InvariantCulture, App_Data.SqlQueries.CountFileTables);
                using (var command = new SqlCommand(query))
                {
                    try
                    {
                        command.Connection = connection;

                        return (int)command.ExecuteScalar();
                    }
                    catch (Exception)
                    {
                        var errorInformation = String.Format(CultureInfo.CurrentCulture, App_Data.ExceptionMessages.FormatDatabase, database);
                        var errorMessage = String.Format(CultureInfo.CurrentCulture, App_Data.ExceptionMessages.CountFiletablesError);
                        throw new SqlExecutionException(errorMessage + Environment.NewLine + errorInformation);
                    }
                }
            }
        }

        /// <summary>
        /// Returns the subset of databases with filetables
        /// </summary>
        /// <param name="sqlInstance">
        /// The sql Instance.
        /// </param>
        /// <param name="database">
        /// The database.
        /// </param>
        /// <returns>
        /// The filetable database list.
        /// </returns>
        public IEnumerable<string> GetFileTableList(string sqlInstance, string database)
        {
            var list = new List<string>();

            using (var connection = ConnectServerInstance(sqlInstance, database).ConnectionContext.SqlConnectionObject)
            {
                var query = String.Format(CultureInfo.InvariantCulture, App_Data.SqlQueries.ListFileTables);
                using (var command = new SqlCommand(query))
                {
                    try
                    {
                        command.Connection = connection;

                        using (var reader = command.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                list.Add(reader.GetString(0));
                            }
                        }
                    }
                    catch
                    {
                        var errorInformation = String.Format(CultureInfo.CurrentCulture, App_Data.ExceptionMessages.FormatDatabase, database);
                        var errorMessage = App_Data.ExceptionMessages.GetFiletableListError;

                        throw new SqlExecutionException(errorMessage + Environment.NewLine + errorInformation);
                    }
                }
            }

            return list;
        }

        /// <summary>
        /// Create a new File Table.
        /// </summary>
        /// <param name="sqlInstance">
        /// The sql Instance.
        /// </param>
        /// <param name="database">
        /// The database.
        /// </param>
        /// <param name="fileTableName">
        /// The filetable.
        /// </param>
        /// <param name="createDatabase">
        /// The new database.
        /// </param>
        /// <returns>
        /// True if the filetable was created succesfully, false otherwise.
        /// </returns>
        public bool CreateFileTable(string sqlInstance, string database, string fileTableName, bool createDatabase)
        {
            var sqlServer = ConnectServerInstance(sqlInstance, String.Empty);

            Database db;
            if (createDatabase)
            {
                db = CreateDatabase(database, sqlServer);
            }
            else
            {
                db = sqlServer.Databases[database];
                if (db == null)
                {
                    var errorInformation = String.Format(CultureInfo.CurrentCulture, App_Data.ExceptionMessages.FormatDatabase, database);
                    var errorMessage = App_Data.ExceptionMessages.DatabaseNotFoundError;

                    throw new SmoException(errorMessage + Environment.NewLine + errorInformation);
                }
            }

            FullTextCatalog fullTextCatalog;
            if (db.FullTextCatalogs.Count == 0)
            {
                try
                {
                    fullTextCatalog = new FullTextCatalog(db, App_Data.FileTableElements.FullTextCatalogName) { IsDefault = true };
                    fullTextCatalog.Create();
                }
                catch
                {
                    var errorInformation = String.Format(CultureInfo.CurrentCulture, App_Data.ExceptionMessages.FormatDatabase, database) + Environment.NewLine +
                        String.Format(CultureInfo.CurrentCulture, App_Data.ExceptionMessages.FormatFileTable, fileTableName);
                    var errorMessage = App_Data.ExceptionMessages.FullTextCatalogCreationError;

                    throw new SmoException(errorMessage + Environment.NewLine + errorInformation);
                }
            }
            else
            {
                fullTextCatalog = db.FullTextCatalogs[db.DefaultFullTextCatalog];
            }

            Table fileTable;
            try
            {
                fileTable = new Table(db, fileTableName) { IsFileTable = true };
                fileTable.Create();
            }
            catch
            {
                var errorInformation = String.Format(CultureInfo.CurrentCulture, App_Data.ExceptionMessages.FormatDatabase, database) + Environment.NewLine +
                        String.Format(CultureInfo.CurrentCulture, App_Data.ExceptionMessages.FormatFileTable, fileTableName);
                var errorMessage = App_Data.ExceptionMessages.FileTableCreationError;

                throw new SmoException(errorMessage + Environment.NewLine + errorInformation);
            }

            Index fileTableIndex;
            try
            {
                fileTableIndex = new Index(fileTable, App_Data.FileTableElements.FileTableIndexPrefix + fileTableName) { IsUnique = true };
                var indexColumn = new IndexedColumn(fileTableIndex, App_Data.FileTableElements.FileTableIndexedColumn, false);
                fileTableIndex.IndexedColumns.Add(indexColumn);
                fileTableIndex.Create();
            }
            catch (Exception)
            {
                throw new SmoException(App_Data.ExceptionMessages.FileTableIndexCreationError);
            }

            var searchPropertyList = db.SearchPropertyLists[App_Data.FileTableElements.SearchPropertyListName] ?? CreateSearchPropertyList(db);

            try
            {
                var fullTextIndex = new FullTextIndex(fileTable)
                {
                    CatalogName = fullTextCatalog.Name,
                    UniqueIndexName = fileTableIndex.Name,
                    SearchPropertyListName = searchPropertyList.Name
                };
                var fullTextIndexColumn = new FullTextIndexColumn(
                fullTextIndex, App_Data.FileTableElements.FullTextIndexColumn)
                {
                    Language = App_Data.FileTableElements.FullTextIndexColumnLanguage,
                    StatisticalSemantics = 1,
                    TypeColumnName = App_Data.FileTableElements.FullTextIndexColumnType
                };

                fullTextIndex.IndexedColumns.Add(fullTextIndexColumn);
                fullTextIndex.Create();
            }
            catch
            {
                throw new SmoException(App_Data.ExceptionMessages.FullTextIndexCreationError);
            }

            return true;
        }

        /// <summary>
        /// Checks if the SQL Server instance is available and can connect to it
        /// </summary>
        /// <param name="sqlInstance">
        /// The sql instance.
        /// </param>
        /// <returns>
        /// A boolean indicating whether the SQL Server Instance is available
        /// </returns>
        public string CheckServerConnection(string sqlInstance = "")
        {
            if (String.IsNullOrEmpty(sqlInstance))
            {
                sqlInstance = Environment.MachineName;
            }

            ConnectServerInstance(sqlInstance);
            return sqlInstance;
        }

        /// <summary>
        /// Given a tag this method queries the database for a list of documents associated with the tag along with the score
        /// </summary>
        /// <param name="sqlInstance">
        /// The sql Instance.
        /// </param>
        /// <param name="database">
        /// The database.
        /// </param>
        /// <param name="filetable">
        /// The filetable.
        /// </param>
        /// <param name="tag">
        /// The given tag
        /// </param>
        /// <returns>
        /// A dictionary with the documents as keys and the scores as values
        /// </returns>
        public IDictionary<string, double> GetDocumentsByTag(string sqlInstance, string database, string filetable, string tag)
        {
            var query = String.Format(App_Data.SqlQueries.GetDocumentsByTag, filetable);
            using (var command = new SqlCommand(query))
            {
                try
                {
                    command.Parameters.AddWithValue("@tag", tag);

                    return GetDocumentsByTagCommand(sqlInstance, database, command);
                }
                catch (Exception)
                {
                    var errorInformation = String.Format(CultureInfo.CurrentCulture, App_Data.ExceptionMessages.FormatDatabase, database) + Environment.NewLine +
                        String.Format(CultureInfo.CurrentCulture, App_Data.ExceptionMessages.FormatTag, tag) + Environment.NewLine +
                        String.Format(CultureInfo.CurrentCulture, App_Data.ExceptionMessages.FormatFileTable, filetable);
                    var errorMessage = App_Data.ExceptionMessages.GetTagsDocumentError;

                    throw new SqlExecutionException(errorMessage + Environment.NewLine + errorInformation);
                }
            }
        }

        /// <summary>
        /// Given a document this method queries the database for a list of related documents
        /// </summary>
        /// <param name="sqlInstance">
        /// The sql instance.
        /// </param>
        /// <param name="database">
        /// The database.
        /// </param>
        /// <param name="fileTable">
        /// The file table.
        /// </param>
        /// <param name="docName">
        /// The doc name.
        /// </param>
        /// <returns>
        /// A dictionary with doc names as keys and score as values
        /// </returns>
        public IDictionary<string, double> GetRelatedDocuments(string sqlInstance, string database, string fileTable, string docName)
        {
            var related = new Dictionary<string, double>();
            var docId = new SqlGuid();

            using (var connection = ConnectServerInstance(sqlInstance, database).ConnectionContext.SqlConnectionObject)
            {
                var query = String.Format(CultureInfo.InvariantCulture, App_Data.SqlQueries.GetDocumentID, fileTable);
                using (var command = new SqlCommand(query))
                {
                    try
                    {
                        command.Connection = connection;
                        command.Parameters.AddWithValue("@Title", docName);

                        using (var reader = command.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                docId = reader.GetSqlGuid(0);
                            }
                        }
                    }
                    catch
                    {
                        var errorInformation = String.Format(CultureInfo.CurrentCulture, App_Data.ExceptionMessages.FormatDatabase, database) + Environment.NewLine +
                            String.Format(CultureInfo.CurrentCulture, App_Data.ExceptionMessages.FormatFileTable, fileTable) + Environment.NewLine;
                        var errorMessage = App_Data.ExceptionMessages.GetDocumentTagsError;

                        throw new SqlExecutionException(errorMessage + Environment.NewLine + errorInformation);
                    }
                }

                query = String.Format(CultureInfo.InvariantCulture, App_Data.SqlQueries.GetRelatedDocuments, fileTable);
                using (var command = new SqlCommand(query))
                {
                    try
                    {
                        command.Connection = connection;
                        command.Parameters.AddWithValue("@DocID", docId.ToSqlString());

                        using (var reader = command.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                related[reader.GetString(0)] = Math.Round(reader.GetFloat(1), roundScore);
                            }
                        }
                    }
                    catch
                    {
                        var errorInformation = String.Format(CultureInfo.CurrentCulture, App_Data.ExceptionMessages.FormatDatabase, database) + Environment.NewLine +
                            String.Format(CultureInfo.CurrentCulture, App_Data.ExceptionMessages.FormatFileTable, fileTable) + Environment.NewLine;
                        var errorMessage = App_Data.ExceptionMessages.GetDocumentTagsError;

                        throw new SqlExecutionException(errorMessage + Environment.NewLine + errorInformation);
                    }
                }
            }

            return related;
        }

        /// <summary>
        /// Retrieves a set of tags along with their scores related to a given set of documents
        /// </summary>
        /// <param name="sqlInstance">
        /// The sql instance.
        /// </param>
        /// <param name="database">
        /// The database.
        /// </param>
        /// <param name="fileTable">
        /// The file table.
        /// </param>
        /// <param name="docNames">
        /// The doc names.
        /// </param>
        /// <param name="tagCount">
        /// The tag count.
        /// </param>
        /// <returns>
        /// A IDictionary with tags as keys and score as values
        /// </returns>
        /// <exception cref="SqlExecutionException">
        /// </exception>
        public IDictionary<string, double> GetTagsForDocuments(string sqlInstance, string database, string fileTable, List<string> docNames, int tagCount)
        {
            var tags = new Dictionary<string, double>();

            using (var connection = ConnectServerInstance(sqlInstance, database).ConnectionContext.SqlConnectionObject)
            {
                var names = string.Empty;
                if (docNames.Count > 1)
                {
                    names = docNames.Aggregate(names, (current, docName) => string.Equals(docNames.Last(), docName) ? string.Concat(current, "'", docName, "'") : string.Concat(current, "'", docName, "', "));
                }
                else if (docNames.Count() == 1)
                {
                    names = string.Concat("'", docNames.First(), "'");
                }

                var query = String.Format(CultureInfo.InvariantCulture, App_Data.SqlQueries.GetTagsFromDocuments, fileTable, names);
                using (var command = new SqlCommand(query))
                {
                    try
                    {
                        command.Connection = connection;
                        command.Parameters.AddWithValue("@TagCount", tagCount);

                        using (var reader = command.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                tags[reader.GetString(0)] = Math.Round(reader.GetDouble(1), roundScore);
                            }
                        }
                    }
                    catch
                    {
                        var errorInformation = String.Format(CultureInfo.CurrentCulture, App_Data.ExceptionMessages.FormatDatabase, database) + Environment.NewLine +
                            String.Format(CultureInfo.CurrentCulture, App_Data.ExceptionMessages.FormatFileTable, fileTable) + Environment.NewLine +
                            String.Format(CultureInfo.CurrentCulture, App_Data.ExceptionMessages.FormatDocumentNames, names);
                        var errorMessage = App_Data.ExceptionMessages.GetDocumentTagsError;

                        throw new SqlExecutionException(errorMessage + Environment.NewLine + errorInformation);
                    }
                }
            }

            return tags;
        }

        /// <summary>
        /// Overrides default OnError behaviour when an exception occurs
        /// </summary>
        /// <param name="errorInfo">
        /// The error info.
        /// </param>
        protected override void OnError(DomainServiceErrorInfo errorInfo)
        {
            if (errorInfo == null)
            {
                return;
            }

            var serviceException = errorInfo.Error;
            if (serviceException == null)
            {
                return;
            }

            throw new DomainException(serviceException.Message);
        }

        /// <summary>
        /// Checks whether a specific Filetable exists in a database
        /// </summary>
        /// <param name="sqlInstance">
        /// The sql Instance.
        /// </param>
        /// <param name="database">
        /// The database.
        /// </param>
        /// <param name="filetable">
        /// The filetable.
        /// </param>
        /// <returns>
        /// A boolean value indicating whether the Filetable exists
        /// </returns>
        private static bool ExistsFileTable(string sqlInstance, string database, string filetable)
        {
            using (var connection = ConnectServerInstance(sqlInstance, database).ConnectionContext.SqlConnectionObject)
            {
                using (var command = new SqlCommand(App_Data.SqlQueries.CheckFileTableExists))
                {
                    try
                    {
                        command.Connection = connection;
                        command.Parameters.AddWithValue("@filetable", filetable);

                        var propertyCheck = (int)command.ExecuteScalar();
                        return propertyCheck != 0;
                    }
                    catch
                    {
                        var errorInformation = String.Format(CultureInfo.CurrentCulture, App_Data.ExceptionMessages.FormatDatabase, database) + Environment.NewLine +
                            String.Format(CultureInfo.CurrentCulture, App_Data.ExceptionMessages.FormatFileTable, filetable);
                        var errorMessage = App_Data.ExceptionMessages.ExistsFileTableError;

                        throw new SqlExecutionException(errorMessage + Environment.NewLine + errorInformation);
                    }
                }
            }
        }

        /// <summary>
        /// Checks whether a specific search property exists associated with a Filetable
        /// </summary>
        /// <param name="sqlInstance">
        /// The sql Instance.
        /// </param>
        /// <param name="database">
        /// The database.
        /// </param>
        /// <param name="filetable">
        /// The filetable.
        /// </param>
        /// <param name="property">
        /// The property.
        /// </param>
        /// <returns>
        /// A boolean indicating whether the specific search property exists associated with the Filetable
        /// </returns>
        private static bool ExistsSearchProperty(string sqlInstance, string database, string filetable, string property)
        {
            using (var connection = ConnectServerInstance(sqlInstance, database).ConnectionContext.SqlConnectionObject)
            {
                using (var command = new SqlCommand(App_Data.SqlQueries.CheckSearchPropertyExists))
                {
                    try
                    {
                        command.Connection = connection;
                        command.Parameters.AddWithValue("@property", property);
                        command.Parameters.AddWithValue("@filetable", filetable);

                        var propertyCheck = (int)command.ExecuteScalar();

                        return propertyCheck != 0;
                    }
                    catch
                    {
                        var errorInformation = String.Format(CultureInfo.CurrentCulture, App_Data.ExceptionMessages.FormatDatabase, database) + Environment.NewLine +
                            String.Format(CultureInfo.CurrentCulture, App_Data.ExceptionMessages.FormatFileTable, filetable) + Environment.NewLine +
                            String.Format(CultureInfo.CurrentCulture, App_Data.ExceptionMessages.FormatProperty, property);
                        var errorMessage = App_Data.ExceptionMessages.ExistsSearchPropertyError;

                        throw new SqlExecutionException(errorMessage + Environment.NewLine + errorInformation);
                    }
                }
            }
        }

        /// <summary>
        /// Creates a Search Property List in a database
        /// </summary>
        /// <param name="database">
        /// The database.
        /// </param>
        /// <returns>
        /// The Search Property List object
        /// </returns>
        private static SearchPropertyList CreateSearchPropertyList(Database database)
        {
            try
            {
                var searchPropertyList = new SearchPropertyList(database, App_Data.FileTableElements.SearchPropertyListName);
                searchPropertyList.Create();

                var resourceSet = App_Data.SearchProperties.ResourceManager.GetResourceSet(CultureInfo.CurrentUICulture, true, true);
                foreach (DictionaryEntry entry in resourceSet)
                {
                    var name = (string)entry.Key;
                    var description = (string)entry.Value;
                    var guid = App_Data.SearchPropertiesValues.ResourceManager.GetString(name + "_GUID");
                    var id = Int32.Parse(App_Data.SearchPropertiesValues.ResourceManager.GetString(name + "_ID"), CultureInfo.InvariantCulture);

                    new SearchProperty(searchPropertyList, name, guid, id, description).Create();
                }

                searchPropertyList.Alter();

                return searchPropertyList;
            }
            catch
            {
                var errorInformation = String.Format(CultureInfo.CurrentCulture, App_Data.ExceptionMessages.FormatDatabase, database);
                var errorMessage = App_Data.ExceptionMessages.CreateSearchPropertyListError;

                throw new SmoException(errorMessage + Environment.NewLine + errorInformation);
            }
        }

        /// <summary>
        /// Creates a new database that supports Filetables in the SQL Server instance
        /// </summary>
        /// <param name="database">
        /// The database.
        /// </param>
        /// <param name="server">
        /// The server.
        /// </param>
        /// <returns>
        /// A new database
        /// </returns>
        private static Database CreateDatabase(string database, Server server)
        {
            try
            {
                var sqlDataFolder = server.InstallDataDirectory + @"\DATA\";

                var mdfFile = sqlDataFolder + database + App_Data.DatabaseElements.mdfFileSuffix;
                var ldfFile = sqlDataFolder + database + App_Data.DatabaseElements.ldfFileSuffix;
                var filesDir = sqlDataFolder + database;

                var dataBase = new Database(server, database);
                dataBase.FileGroups.Add(new FileGroup(dataBase, App_Data.DatabaseElements.PrimaryFileGroupName));
                dataBase.FileGroups.Add(new FileGroup(dataBase, App_Data.DatabaseElements.FilestreamFileGroupPrefix + dataBase, true));

                var primaryDataFile = new DataFile(
                    dataBase.FileGroups[App_Data.DatabaseElements.PrimaryFileGroupName],
                    App_Data.DatabaseElements.PrimaryFileGroupDatafile,
                    mdfFile)
                {
                    Size = Double.Parse(App_Data.DatabaseElements.PrimaryFileGroupDatafileSize),
                    GrowthType = FileGrowthType.KB,
                    Growth = Double.Parse(App_Data.DatabaseElements.PrimaryFileGroupDatafileGrowth)
                };

                var filestreamDataFile =
                    new DataFile(
                        dataBase.FileGroups[App_Data.DatabaseElements.FilestreamFileGroupPrefix + dataBase],
                        App_Data.DatabaseElements.FileStreamDataFilePrefix + dataBase,
                        filesDir);

                dataBase.FileGroups[App_Data.DatabaseElements.PrimaryFileGroupName].Files.Add(primaryDataFile);
                dataBase.FileGroups[App_Data.DatabaseElements.FilestreamFileGroupPrefix + dataBase].Files.Add(
                    filestreamDataFile);

                var logFile = new LogFile(dataBase, App_Data.DatabaseElements.LogFileName, ldfFile)
                {
                    Size = Double.Parse(App_Data.DatabaseElements.LogFileSize),
                    GrowthType = FileGrowthType.Percent,
                    Growth = Double.Parse(App_Data.DatabaseElements.LogFileGrowth)
                };
                dataBase.LogFiles.Add(logFile);

                dataBase.FilestreamNonTransactedAccess = FilestreamNonTransactedAccessType.Full;
                dataBase.FilestreamDirectoryName = System.IO.Path.GetRandomFileName();

                dataBase.Create();

                return dataBase;
            }
            catch
            {
                var errorInformation = String.Format(CultureInfo.CurrentCulture, App_Data.ExceptionMessages.FormatDatabase, database);
                var errorMessage = App_Data.ExceptionMessages.CreateDatabaseError;

                throw new SmoException(errorMessage + Environment.NewLine + errorInformation);
            }
        }

        /// <summary>
        /// Executes a SQL command that retrives documents from the database.
        /// </summary>
        /// <param name="sqlInstance">
        /// The sql Instance.
        /// </param>
        /// <param name="database">
        /// The database.
        /// </param>
        /// <param name="command">
        /// The SQL command to retrive documents.
        /// </param>
        /// <returns>
        /// The documents selected in the command.
        /// </returns>
        private static IDictionary<string, string> GetDocumentsCommand(string sqlInstance, string database, SqlCommand command)
        {
            var documents = new Dictionary<string, string>();

            using (var connection = ConnectServerInstance(sqlInstance, database).ConnectionContext.SqlConnectionObject)
            {
                try
                {
                    command.Connection = connection;

                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            documents[reader.GetString(0)] = reader.GetString(1);
                        }
                    }
                }
                catch
                {
                    var errorInformation = String.Format(CultureInfo.CurrentCulture, App_Data.ExceptionMessages.FormatDatabase, database) + Environment.NewLine +
                            String.Format(CultureInfo.CurrentCulture, App_Data.ExceptionMessages.FormatSQLCommand, command.CommandText);
                    var errorMessage = App_Data.ExceptionMessages.GetDocumentsError;

                    throw new SqlExecutionException(errorMessage + Environment.NewLine + errorInformation);
                }
            }

            return documents;
        }

        /// <summary>
        /// Executes a SQL command that retrives documents related to a particular tag from the database, and the relevance of each document to that tag
        /// </summary>
        /// <param name="sqlInstance">
        /// The sql instance.
        /// </param>
        /// <param name="database">
        /// The database.
        /// </param>
        /// <param name="command">
        /// The command.
        /// </param>
        /// <returns>
        /// Returns a dictionary with the document's title as key and the score as value
        /// </returns>
        private static IDictionary<string, double> GetDocumentsByTagCommand(string sqlInstance, string database, SqlCommand command)
        {
            var documents = new Dictionary<string, double>();

            using (var connection = ConnectServerInstance(sqlInstance, database).ConnectionContext.SqlConnectionObject)
            {
                try
                {
                    command.Connection = connection;

                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            documents[reader.GetString(0)] = Math.Round(reader.GetFloat(1), roundScore);
                        }
                    }
                }
                catch
                {
                    var errorInformation = String.Format(CultureInfo.CurrentCulture, App_Data.ExceptionMessages.FormatDatabase, database) + Environment.NewLine +
                            String.Format(CultureInfo.CurrentCulture, App_Data.ExceptionMessages.FormatSQLCommand, command.CommandText);
                    var errorMessage = App_Data.ExceptionMessages.GetDocumentsError;

                    throw new SqlExecutionException(errorMessage + Environment.NewLine + errorInformation);
                }
            }

            return documents;
        }

        /// <summary>
        /// Creates an object that connects to the SQL Server instance
        /// </summary>
        /// <param name="sqlInstance">
        /// The sql Instance.
        /// </param>
        /// <param name="database">
        /// The database.
        /// </param>
        /// <returns>
        /// A Server instance
        /// </returns>
        private static Server ConnectServerInstance(string sqlInstance = "", string database = "")
        {
            try
            {
                var sqlServer = new Server(sqlInstance);
                sqlServer.ConnectionContext.LoginSecure = true;
                sqlServer.ConnectionContext.DatabaseName = database;
                sqlServer.ConnectionContext.Connect();
                return sqlServer;
            }
            catch
            {
                var errorInformation = String.Format(CultureInfo.CurrentCulture, App_Data.ExceptionMessages.FormatSqlServerInstance, sqlInstance);
                var errorMessage = App_Data.ExceptionMessages.ConnectServerInstanceError;

                throw new SmoException(errorMessage + Environment.NewLine + errorInformation);
            }
        }
    }
}
